/*
 * Copyright (C) 2006 The Android Open Source Project
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
// modified from original source see README at the top level of this project

package io.requery.ohos.sqlite.demo;

import io.requery.ohos.database.DefaultDatabaseErrorHandler;
import io.requery.ohos.database.sqlite.SQLiteDatabase;
import io.requery.ohos.database.sqlite.SQLiteStatement;

import ohos.aafwk.ability.delegation.AbilityDelegatorRegistry;
import ohos.data.rdb.ValuesBucket;
import ohos.app.Context;
import ohos.data.resultset.ResultSet;
import ohos.hiviewdfx.HiLog;
import ohos.hiviewdfx.HiLogLabel;
import ohos.utils.Pair;

import net.sqlcipher.database.SQLiteException;

import junit.framework.Assert;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertSame;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

/**DatabaseGeneralTest
 *
 */
public class DatabaseGeneralTest {
    private static final String TAG = "DatabaseGeneralTest";
    private static final HiLogLabel LABEL = new HiLogLabel(HiLog.LOG_APP, 0x002A, TAG);

    private static final String STRING = "this is a test";
    private static final String STRING1 = "and yet another test";
    private static final String STRING2 = "this string is a little longer, but still a test";
    private static final String PHONE_NUMBER = "16175551212";
    private static final int TABLE_INFO_PRAGMA_COLUMNNAME_INDEX = 1;
    private static final int TABLE_INFO_PRAGMA_DEFAULT_INDEX = 4;

    private static final int CURRENT_DATABASE_VERSION = 42;
    private SQLiteDatabase mDatabase;
    private File mDatabaseFile;

    /**
     * setUp
     */
    @Before
    public void setUp() {
        File dbDir = AbilityDelegatorRegistry.getAbilityDelegator().getAppContext().getDir(this.getClass().getName(),
                Context.MODE_PRIVATE);
        mDatabaseFile = new File(dbDir, "database_test.db");
        if (mDatabaseFile.exists()) {
            if (mDatabaseFile.delete()) {
                HiLog.info(LABEL, "database deleted");
            }
        }
        mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null);
        assertNotNull(mDatabase);
        mDatabase.setVersion(CURRENT_DATABASE_VERSION);
    }

    /**
     * tearDown
     */
    @After
    public void tearDown() {
        mDatabase.close();
        if (mDatabaseFile.delete()) {
            HiLog.info(LABEL, "Database deleted");
        }
    }

    private void populateDefaultTable() {
        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");

        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + STRING + "');");
        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + STRING1 + "');");
        mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + STRING2 + "');");
    }

    /** testCustomFunction
     *
     */
    @Test
    public void testCustomFunction() {
        mDatabase.addCustomFunction("roundFunction", 1, new SQLiteDatabase.CustomFunction() {
            @Override
            public String callback(String[] args) {
                String input = args[0];
                double value = Double.parseDouble(input);
                return String.valueOf(Math.round(value));
            }
        });
        ResultSet cursor = mDatabase.rawQuery("SELECT roundFunction(3.14)", null);
        assertTrue(cursor.goToFirstRow());
        int result = cursor.getInt(0);
        assertSame(3, result);
    }

    /** testNewFunction
     *
     */
    @Test
    public void testNewFunction() {
        mDatabase.addFunction("roundFunction2", 1, new SQLiteDatabase.Function() {
            @Override
            public void callback(Args args, Result result) {
                double value = args.getDouble(0);
                result.set(Math.round(value));
            }
        });
        ResultSet cursor = mDatabase.rawQuery("SELECT roundFunction2(3.14)", null);
        assertTrue(cursor.goToFirstRow());
        int result = cursor.getInt(0);
        assertSame(3, result);
    }

    /** testCustomFunctionNoReturn
     *
     */
    @Test
    public void testCustomFunctionNoReturn() {
        mDatabase.addCustomFunction("emptyFunction", 1, new SQLiteDatabase.CustomFunction() {
            @Override
            public String callback(String[] args) {
                String nullValue = null;
                return nullValue;
            }
        });
        ResultSet cursor = mDatabase.rawQuery("SELECT emptyFunction(3.14)", null);
        // always empty regardless of if sqlite3_result_null is called or not
        cursor.goToFirstRow();
        assertSame(null, cursor.getString(0));
    }

    /** testNewFunctionNoReturn
     *
     */
    @Test
    public void testNewFunctionNoReturn() {
        mDatabase.addFunction("emptyFunction2", 1, new SQLiteDatabase.Function() {
            @Override
            public void callback(Args args, Result result) {
            }
        });
        ResultSet cursor = mDatabase.rawQuery("SELECT emptyFunction2(3.14)", null);
        // always empty regardless of if sqlite3_result_null is called or not
        cursor.goToFirstRow();
        assertSame(null, cursor.getString(0));
    }

    /** testVersion
     *
     */
    @Test
    public void testVersion() {
        assertEquals(CURRENT_DATABASE_VERSION, mDatabase.getVersion());
        mDatabase.setVersion(11);
        assertEquals(11, mDatabase.getVersion());
    }

    /** testUpdate
     *
     */
    @Test
    public void testUpdate() {
        populateDefaultTable();

        ValuesBucket values = new ValuesBucket(1);
        values.putString("data", "this is an updated test");
        assertEquals(1, mDatabase.update("test", values, "_id=1", null));
        ResultSet query = mDatabase.query("test", null, "_id=1", null, null, null, null);
        assertNotNull(query);
        assertEquals(1, query.getRowCount());
        query.goToFirstRow();
        String value = query.getString(query.getColumnIndexForName("data"));
        assertEquals("this is an updated test", value);
    }

    /** testSupportUpdate
     *
     */
    @Test
    public void testSupportUpdate() {
        populateDefaultTable();

        ValuesBucket values = new ValuesBucket(1);
        values.putString("data", "this is an updated test");
        assertEquals(1, mDatabase.update("test", SQLiteDatabase.CONFLICT_NONE, values,
                "_id=?", new Object[] { 1 }));
        ResultSet test = mDatabase.query("test", null, "_id=1", null, null, null, null);
        assertNotNull(test);
        assertEquals(1, test.getRowCount());
        test.goToFirstRow();
        String value = test.getString(test.getColumnIndexForName("data"));
        assertEquals("this is an updated test", value);
    }

    /** testSupportDelete
     *
     */
    @Test
    public void testSupportDelete() {
        populateDefaultTable();
        assertEquals(1, mDatabase.delete("test", "_id=?", new Object[] { 1 }));
        ResultSet resultSet = mDatabase.query("test", null, "_id=1", null, null, null, null);
        assertNotNull(resultSet);
        assertEquals(0, resultSet.getRowCount());
    }

    /** testSchemaChange1
     * Tests international matching issues for the PHONE_NUMBERS_EQUAL function.
     * @throws Exception
     */
    @Test
    public void testSchemaChange1() throws Exception {
        SQLiteDatabase db1 = mDatabase;
        ResultSet cursor;

        db1.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);");

        cursor = db1.query("db1", null, null, null, null, null, null);
        assertNotNull("ResultSet is null", cursor);

        db1.execSQL("CREATE TABLE db2 (_id INTEGER PRIMARY KEY, data TEXT);");

        assertEquals(0, cursor.getRowCount());
        cursor.close();
    }

    /** testSchemaChange2
     *
     */
    @Test
    public void testSchemaChange2() {
        mDatabase.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);");
        ResultSet cursor = mDatabase.query("db1", null, null, null, null, null, null);
        assertNotNull(cursor);
        assertEquals(0, cursor.getRowCount());
        cursor.close();
    }

    /** testSchemaChange3
     *
     */
    @Test
    public void testSchemaChange3() {
        mDatabase.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);");
        mDatabase.execSQL("INSERT INTO db1 (data) VALUES ('test');");
        mDatabase.execSQL("ALTER TABLE db1 ADD COLUMN blah int;");
        ResultSet resultSet = null;
        try {
            resultSet = mDatabase.rawQuery("select blah from db1", null);
        } catch (SQLiteException e) {
            fail("unexpected exception: " + e.getMessage());
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
        }
    }

    /** testSelectionArgs
     *
     */
    @Test
    public void testSelectionArgs() {
        mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");
        ValuesBucket values = new ValuesBucket(1);
        values.putString("data", "don't forget to handled 's");
        mDatabase.insert("test", "data", values);
        values.clear();
        values.putString("data", "no apostrophes here");
        mDatabase.insert("test", "data", values);
        ResultSet resultSet = mDatabase.query(
                "test", null, "data GLOB ?", new String[]{"*'*"}, null, null, null);
        assertEquals(1, resultSet.getRowCount());
        assertTrue(resultSet.goToFirstRow());
        assertEquals("don't forget to handled 's", resultSet.getString(1));
        resultSet.close();
    }

    /** testTransactions
     *
     */
    @Test
    public void testTransactions() {
        mDatabase.execSQL("CREATE TABLE test (num INTEGER);");
        mDatabase.execSQL("INSERT INTO test (num) VALUES (0)");

        // Make sure that things work outside an explicit transaction.
        setNum(1);
        checkNum(1);

        // Test a single-level transaction.
        setNum(0);
        mDatabase.beginTransaction();
        setNum(1);
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
        checkNum(1);
        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());

        // Test a rolled-back transaction.
        setNum(0);
        mDatabase.beginTransaction();
        setNum(1);
        mDatabase.endTransaction();
        checkNum(0);
        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());

        // We should get an error if we end a non-existent transaction.
        assertThrowsIllegalState(new Runnable() {
            /**
             * run
             */
        public void run() {
            mDatabase.endTransaction();
        }});

        // We should get an error if a set a non-existent transaction as clean.
        assertThrowsIllegalState(new Runnable() {
            /**
             * run
             */
            public void run() {
            mDatabase.setTransactionSuccessful();
        }});

        mDatabase.beginTransaction();
        mDatabase.setTransactionSuccessful();
        // We should get an error if we mark a transaction as clean twice.
        assertThrowsIllegalState(new Runnable() {
            /**
             * run
             */
            public void run() {
            mDatabase.setTransactionSuccessful();
        }});
        // We should get an error if we begin a transaction after marking the parent as clean.
        assertThrowsIllegalState(new Runnable() {
            /**
             * run
             */
            public void run() {
            mDatabase.beginTransaction();
        }});
        mDatabase.endTransaction();
        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());

        // Test a two-level transaction.
        setNum(0);
        mDatabase.beginTransaction();
        mDatabase.beginTransaction();
        setNum(1);
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
        mDatabase.setTransactionSuccessful();
        mDatabase.endTransaction();
        checkNum(1);
        Assert.assertFalse(mDatabase.isDbLockedByCurrentThread());
    }

    private void setNum(int num) {
        mDatabase.execSQL("UPDATE test SET num = " + num);
    }

    private void checkNum(int num) {
        Assert.assertEquals(
                num, longForQuery(mDatabase, "SELECT num FROM test", null));
    }

    private void assertThrowsIllegalState(Runnable runnable) {
        boolean ok = false;
        try {
            runnable.run();
        } catch (IllegalStateException e) {
            ok = true;
        }
        Assert.assertTrue(ok);
    }

    /** testTableInfoPragma
     *
     */
    @Test
    public void testTableInfoPragma() {
        mDatabase.execSQL("CREATE TABLE pragma_test (" +
                "i INTEGER DEFAULT 1234, " +
                "j INTEGER, " +
                "s TEXT DEFAULT 'hello', " +
                "t TEXT, " +
                "'select' TEXT DEFAULT \"hello\")");
        ResultSet cur = mDatabase.rawQuery("PRAGMA table_info(pragma_test)", null);
        Assert.assertEquals(5, cur.getRowCount());

        Assert.assertTrue(cur.goToNextRow());
        Assert.assertEquals("i",
                cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
        Assert.assertEquals("1234",
                cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));

        Assert.assertTrue(cur.goToNextRow());
        Assert.assertEquals("j",
                cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
        Assert.assertEquals(null, cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));

        Assert.assertTrue(cur.goToNextRow());
        Assert.assertEquals("s",
                cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
        Assert.assertEquals("'hello'",
                cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));

        Assert.assertTrue(cur.goToNextRow());
        Assert.assertEquals("t",
                cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
        Assert.assertEquals(null, cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));

        Assert.assertTrue(cur.goToNextRow());
        Assert.assertEquals("select",
                cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX));
        Assert.assertEquals("\"hello\"",
                cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX));

        cur.close();
    }

    /** testSemicolonsInStatements
     *
     */
    @Test
    public void testSemicolonsInStatements() {
        mDatabase.execSQL("CREATE TABLE pragma_test (" +
                "i INTEGER DEFAULT 1234, " +
                "j INTEGER, " +
                "s TEXT DEFAULT 'hello', " +
                "t TEXT, " +
                "'select' TEXT DEFAULT \"hello\")");
        // ending the sql statement with  semicolons shouldn't be a problem.
        ResultSet cur = mDatabase.rawQuery("PRAGMA database_list;", null);
        cur.close();
        // two semicolons in the statement shouldn't be a problem.
        cur = mDatabase.rawQuery("PRAGMA database_list;;", null);
        cur.close();
    }

    /** testUnionsWithBindArgs
     *
     */
    @Test
    public void testUnionsWithBindArgs() {
        /* make sure unions with bindargs work http://b/issue?id=1061291 */
        mDatabase.execSQL("CREATE TABLE A (i int);");
        mDatabase.execSQL("create table B (k int);");
        mDatabase.execSQL("create table C (n int);");
        mDatabase.execSQL("insert into A values(1);");
        mDatabase.execSQL("insert into A values(2);");
        mDatabase.execSQL("insert into A values(3);");
        mDatabase.execSQL("insert into B values(201);");
        mDatabase.execSQL("insert into B values(202);");
        mDatabase.execSQL("insert into B values(203);");
        mDatabase.execSQL("insert into C values(901);");
        mDatabase.execSQL("insert into C values(902);");
        String str = "select i from A where i > 2 " +
                "UNION select k from B where k > 201 " +
                "UNION select n from C where n !=900;";
        ResultSet resultSet = mDatabase.rawQuery(str, null);
        int num = resultSet.getRowCount();
        resultSet.close();
        String s1 = "select i from A where i > ? " +
                "UNION select k from B where k > ? " +
                "UNION select n from C where n != ?;";
        ResultSet c1 = mDatabase.rawQuery(s1, new String[]{"2", "201", "900"});
        assertEquals(num, c1.getRowCount());
        c1.close();
    }

    /** testSetMaxCacheSize
     *
     */
    @Test
    public void testSetMaxCacheSize() {
        mDatabase.execSQL("CREATE TABLE test (i int, j int);");
        mDatabase.execSQL("insert into test values(1,1);");
        // set cache size
        int i1 = SQLiteDatabase.MAX_SQL_CACHE_SIZE;
        mDatabase.setMaxSqlCacheSize(i1);

        // try reduce cachesize
        try {
            mDatabase.setMaxSqlCacheSize(1);
        } catch (IllegalStateException e) {
            assertTrue(e.getMessage().contains("cannot set cacheSize to a value less than"));
        }
    }

    private void attachTwice(DefaultDatabaseErrorHandler errorHandler, File dbfile) {
        String attachedDb1File = mDatabase.getPath() + "1";
        SQLiteDatabase dbObj = SQLiteDatabase.openOrCreateDatabase(mDatabase.getPath(), null);
        dbObj.execSQL("ATTACH DATABASE ':memory:' as memoryDb");
        dbObj.execSQL("ATTACH DATABASE '" +  attachedDb1File + "' as attachedDb1");
        assertTrue(dbfile.exists());
        assertTrue(new File(attachedDb1File).exists());
        assertNotNull(dbObj);
        assertTrue(dbObj.isOpen());
        List<Pair<String, String>> attachedDbs = dbObj.getAttachedDbs();
        errorHandler.onCorruption(dbObj);
        assertFalse(dbfile.exists());
        assertFalse(new File(attachedDb1File).exists());
    }

    /** testDefaultDatabaseErrorHandler
     * @throws IOException
     */
    @Test
    public void testDefaultDatabaseErrorHandler() throws IOException {
        DefaultDatabaseErrorHandler errorHandler = new DefaultDatabaseErrorHandler();

        // close the database. and call corruption handler.
        // it should delete the database file.
        File dbfile = new File(mDatabase.getPath());
        mDatabase.close();
        assertFalse(mDatabase.isOpen());
        assertTrue(dbfile.exists());
        errorHandler.onCorruption(mDatabase);
        assertFalse(dbfile.exists());

        // create an in-memory database. and corruption handler shouldn't try to delete it
        SQLiteDatabase memoryDb = SQLiteDatabase.openOrCreateDatabase(":memory:", null);
        assertNotNull(memoryDb);
        memoryDb.close();
        assertFalse(memoryDb.isOpen());
        errorHandler.onCorruption(memoryDb);

        // create a database, keep it open, call corruption handler. database file should be deleted
        SQLiteDatabase dbObj = SQLiteDatabase.openOrCreateDatabase(mDatabase.getPath(), null);
        assertTrue(dbfile.exists());
        assertNotNull(dbObj);
        assertTrue(dbObj.isOpen());
        errorHandler.onCorruption(dbObj);
        assertFalse(dbfile.exists());

        // create a database, attach 2 more databases to it
        // attached database # 1: ":memory:"
        // attached database # 2: mDatabase.getPath() + "1";
        // call corruption handler. database files including the one for attached database # 2
        // should be deleted

        attachTwice(errorHandler, dbfile);

        // same as above, except this is a bit of stress testing. attach 5 database files
        // and make sure they are all removed.

        int i1 = 5;
        ArrayList<String> attachedDbFiles = new ArrayList<String>(i1);
        for (int i = 0; i < i1; i++) {
            attachedDbFiles.add(mDatabase.getPath() + i);
        }
        dbObj = SQLiteDatabase.openOrCreateDatabase(mDatabase.getPath(), null);
        dbObj.execSQL("ATTACH DATABASE ':memory:' as memoryDb");
        for (int i = 0; i < i1; i++) {
            dbObj.execSQL("ATTACH DATABASE '" +  attachedDbFiles.get(i) + "' as attachedDb" + i);
        }
        assertTrue(dbfile.exists());
        for (int i = 0; i < i1; i++) {
            assertTrue(new File(attachedDbFiles.get(i)).exists());
        }
        assertNotNull(dbObj);
        assertTrue(dbObj.isOpen());
        errorHandler.onCorruption(dbObj);
        assertFalse(dbfile.exists());
        for (int i = 0; i < i1; i++) {
            assertFalse(new File(attachedDbFiles.get(i)).exists());
        }
    }

    /**
     * Utility method to run the query on the db and return the value in the
     * first column of the first row.
     * @param db SQLiteDatabase database
     * @param query SQLiteDatabase query
     * @param selectionArgs SQLiteDatabase args
     * @return longForQuery
     */
    private static long longForQuery(SQLiteDatabase db, String query, String[] selectionArgs) {
        SQLiteStatement prog = db.compileStatement(query);
        try {
            return longForQuery(prog, selectionArgs);
        } finally {
            prog.close();
        }
    }

    /**
     * Utility method to run the pre-compiled query and return the value in the
     * first column of the first row.
     * @param prog SQLiteStatement prog
     * @param selectionArgs SQLiteStatement args
     * @return simpleQueryForLong
     */
    private static long longForQuery(SQLiteStatement prog, String[] selectionArgs) {
        prog.bindAllArgsAsStrings(selectionArgs);
        return prog.simpleQueryForLong();
    }

    /**
     * Utility method to run the pre-compiled query and return the value in the
     * first column of the first row.
     * @param prog SQLiteStatement prog
     * @param selectionArgs SQLiteStatement args
     * @return simpleQueryForString
     */
    private static String stringForQuery(SQLiteStatement prog, String[] selectionArgs) {
        prog.bindAllArgsAsStrings(selectionArgs);
        return prog.simpleQueryForString();
    }

}
